package DB;

import DB.ConnectionDB;
import Model.SalesOrder;
import Model.SalesOrderProduct;
import java.sql.*;
import DB.ProductDB;
import DB.SalesOrderDB;

import java.util.ArrayList;

public class SalesOrderProductDB implements SalesOrderProductDBIF{

    private Connection conn;
    private PreparedStatement prepStatement;
    private ProductDB productDB;
    private SalesOrder s;

    public SalesOrderProductDB() {
        conn = ConnectionDB.getInstance().getConn();
        productDB = new ProductDB();
    }

    @Override
    public SalesOrderProduct findSalesOrderProduct(int id) {
        return singleWhere("salesOrderID = "+id,false);
    }

    @Override
    public int insertSalesOrderProduct(SalesOrderProduct salesOrderProduct) {
        int rc = -1;

        String insert = "INSERT INTO SalesOrderProduct (productID, salesOrderID, amount) " +
                "VALUES ("+
                salesOrderProduct.getProduct().getId()  + ","  +
                salesOrderProduct.getSalesOrder().getId() + ","  +
                salesOrderProduct.getAmount()+")";

        System.out.println(insert);
        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(insert  );
            stmt.close();
        }
        catch (SQLException ex){
            System.out.println("Insert error");
        }

        return rc;
    }

    @Override
    public int deleteSalesOrderProduct(int salesOrderID) {
        int rc = -1;

        String query = "DELETE FROM SalesOrderProduct WHERE salesOrderID = " + salesOrderID;

        System.out.println(query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception e){
            System.out.println("Delete exception in SalesOrderProduct db: " + e);
        }
        return(rc);
    }

    public ArrayList<SalesOrderProduct> getSalesOrderProduct(SalesOrder s) {
        this.s = s;
        return miscWhere("salesOrderID =" + s.getId(), false);
    }

    private ArrayList<SalesOrderProduct> miscWhere(String wClause, boolean retrieveAssociation){
        ResultSet results;
        ArrayList<SalesOrderProduct> list = new ArrayList<SalesOrderProduct>();
        String query = buildQuery(wClause);
        System.out.println(query);

        try{
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while( results.next() ){
                SalesOrderProduct salesOrderProduct = buildSalesOrderProduct(results);
                list.add(salesOrderProduct);
            }
            stmt.close();
        }
        catch(Exception e){
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
        return  list;
    }
    private String buildQuery(String wClause) {
        String query = "SELECT productID, salesOrderID, amount FROM SalesOrderProduct";

        if (wClause.length()>0) {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    private SalesOrderProduct buildSalesOrderProduct(ResultSet results){
        SalesOrderProduct salesOrderProduct = new SalesOrderProduct();

        try {
            salesOrderProduct.setProduct(productDB.findProduct(results.getInt("productID")));
            salesOrderProduct.setSalesOrder(s);
            salesOrderProduct.setAmount(results.getInt("amount"));
        }
        catch (SQLException ex){
            System.out.println("Error when trying to build SalesOrderProduct: ");

        }

        return salesOrderProduct;
    }

    private SalesOrderProduct singleWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        SalesOrderProduct salesOrderProduct = new SalesOrderProduct();

        String query = buildQuery(wClause);
        System.out.println(query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next()) {
                salesOrderProduct = buildSalesOrderProduct(results);
                stmt.close();
            }
            else {
                salesOrderProduct = null;
            }
        }
        catch (Exception e) {
            System.out.println("Query exception in SalesOrderProductDB: " + e);
        }

        return salesOrderProduct;
    }
}
